SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
GO

PRINT '---- tblMatchPlayers'

PRINT '-- Table title'
INSERT tblUFGTableTitles (utt_Tablename, utt_Languagename, utt_Title, utt_Description)
VALUES ('tblMatchPlayers','Nederlands','Wedstrijd spelers','Gegevens van wedstrijdspelers')

PRINT '-- Column titles'
INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblMatchPlayers','mpl_HomeClubname','Nederlands','Clubnaam Thuis','U dient een thuisclubnaam in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblMatchPlayers','mpl_HomeSeason','Nederlands','Thuisseizoen','U dient een thuisseizoen in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblMatchPlayers','mpl_HomeTeamName','Nederlands','Thuis teamnaam','U dient een thuisteamnaam in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblMatchPlayers','mpl_AgainstTeamName','Nederlands','Uit teamnaam','U dient een thuisteamnaam in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblMatchPlayers','mpl_AgainstClubname','Nederlands','Clubnaam tegenstander','U dient een clubnaam van de tegenstander in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblMatchPlayers','mpl_AgainstSeason','Nederlands','Seizoen tegenstander','U dient een seizoen in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblMatchPlayers','mpl_Clubname','Nederlands','Clubnaam','U dient een clubnaam in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblMatchPlayers','mpl_Season','Nederlands','Seizoen','U dient een seizoen in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblMatchPlayers','mpl_Teamname','Nederlands','Teamnaam','U dient een theamnaam in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblMatchPlayers','mpl_PersonID','Nederlands','Speler','U dient een speler in te vullen.')

UPDATE tblUFGForeignKeyColumns 
SET ufc_AdditionalWhere = 'dbo.fnGetClubnameByUser(@UFGUsername) IN (tmm_Clubname, ''*'')'
WHERE ufc_ForeignKeyName = 'FK_mpl_Clubname'
AND ufc_TableName = 'tblMatchPlayers'
AND ufc_ColumnName = 'mpl_Clubname'
AND ufc_ReferenceTableName = 'tblTeamMembers'
AND ufc_ReferenceColumnName = 'tmm_Clubname'

UPDATE tblUFGForeignKeyColumns 
SET ufc_AdditionalWhere = 'tmm_Season = CASE dbo.fnGetClubnameByUser(@UFGUsername) WHEN @mat_HomeClubname THEN @mat_HomeSeason ELSE @mat_AgainstSeason END'
WHERE ufc_ForeignKeyName = 'FK_mpl_Clubname'
AND ufc_TableName = 'tblMatchPlayers'
AND ufc_ColumnName = 'mpl_Season'
AND ufc_ReferenceTableName = 'tblTeamMembers'
AND ufc_ReferenceColumnName = 'tmm_Season'

UPDATE tblUFGForeignKeyColumns 
SET ufc_AdditionalWhere = 'tmm_TeamName = CASE dbo.fnGetClubnameByUser(@UFGUsername) WHEN @mat_HomeClubname THEN @mat_HomeTeamName ELSE @mat_AgainstTeamName END'
WHERE ufc_ForeignKeyName = 'FK_mpl_Clubname'
AND ufc_TableName = 'tblMatchPlayers'
AND ufc_ColumnName = 'mpl_TeamName'
AND ufc_ReferenceTableName = 'tblTeamMembers'
AND ufc_ReferenceColumnName = 'tmm_TeamName'

UPDATE tblUFGForeignKeyColumns 
SET ufc_AdditionalWhere = null
WHERE ufc_ForeignKeyName = 'FK_mpl_Clubname'
AND ufc_TableName = 'tblMatchPlayers'
AND ufc_ColumnName = 'mpl_PersonID'
AND ufc_ReferenceTableName = 'tblTeamMembers'
AND ufc_ReferenceColumnName = 'tmm_PersonID'
GO

CREATE TRIGGER tblMatchPlayers_ValidateSuspensions
   ON  tblMatchPlayers
   AFTER INSERT
AS 
BEGIN
	IF @@ROWCOUNT = 0 RETURN
	
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    IF EXISTS (
		SELECT 1 FROM inserted 
		JOIN tblMatches ON mat_HomeClubname = mpl_HomeClubname
			AND mat_HomeTeamName = mpl_HomeTeamName
			AND mat_HomeSeason = mpl_HomeSeason
			AND mat_AgainstClubname = mpl_AgainstClubname
			AND mat_AgainstTeamName = mpl_AgainstTeamName
			AND mat_AgainstSeason = mpl_AgainstSeason
		WHERE dbo.fnGetSuspensions(mpl_PersonID, mat_Date, mpl_Season) > 0)
    BEGIN
		RAISERROR ('SuspendedPlayerSelected', 16, 1)
		ROLLBACK TRANSACTION
    END
END
GO

INSERT dbo.tblUFGMessages
	(umm_Messagename,umm_Languagename,umm_Message)
VALUES
	('SuspendedPlayerSelected', 'Nederlands', 'Geschorste spelers mogen niet worden geslecteerd.')

PRINT '-- Tasks'
INSERT INTO tblUFGSearchFilters (usf_SearchFilterName,usf_SearchFilterType, usf_SearchFilterCondition, usf_RoleName, usf_UserName, usf_TableName, usf_IsWork, usf_Message, usf_Published, usf_Category, usf_PublishedBlock) VALUES
('SuspendedMatchPlayers' ,'Role' ,
 'dbo.fnGetSuspensions(mpl_PersonID, (SELECT mat_Date FROM tblMatches WHERE mat_HomeClubName = mpl_HomeClubname AND mat_HomeSeason = mpl_HomeSeason AND mat_HomeTeamName = mpl_HomeTeamname AND mat_AgainstClubName = mpl_AgainstClubName AND mat_AgainstSeason = mpl_AgainstSeason AND mat_AgainstTeamName = mpl_AgainstTeamName), mpl_Season) > 0 AND (SELECT mat_Date FROM tblMatches WHERE mat_HomeClubName = mpl_HomeClubname AND mat_HomeSeason = mpl_HomeSeason AND mat_HomeTeamName = mpl_HomeTeamname AND mat_AgainstClubName = mpl_AgainstClubName AND mat_AgainstSeason = mpl_AgainstSeason AND mat_AgainstTeamName = mpl_AgainstTeamName) > GETDATE()', 
 'Technische Commissie Club', NULL, 'tblMatchPlayers', 1, 'SF_SuspendedMatchPlayers', 0, NULL, 'NONE')
GO

INSERT dbo.tblUFGMessages (umm_MessageName, umm_LanguageName, umm_Message)
VALUES ('SF_SuspendedMatchPlayers', 'Nederlands', 'Geschorste spelers toch ingepland')
GO